﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace wd_36_sqlite3_v1
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        static public string db3_file = "234.db3";
        static public string g_table_name = "mytable3";
        private void Button_Click1(object sender, RoutedEventArgs e)
        {
            try
            {
                NewDbFile(db3_file);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }            
        }

        static public Boolean NewDbFile(string file_path)
        {
            try
            {
                SQLiteConnection.CreateFile(file_path);
                return true;
            }
            catch (System.Exception ex)
            {
                throw new Exception(string.Format("create db error:{0}", ex.Message));
            }
            //return false; //throw new 
        }

        static SQLiteCommand m_cmd;
        static SQLiteConnection m_sqlconn;


        static public void NewTable(string db_path, string table_name)
        {
            m_sqlconn = new SQLiteConnection("data source=" + db_path);
            if (System.Data.ConnectionState.Open != m_sqlconn.State)
            {
                try
                {
                    m_sqlconn.Open();
                    //SQLiteCommand m_cmd = new SQLiteCommand();
                    m_cmd = new SQLiteCommand();
                    m_cmd.Connection = m_sqlconn;
                    m_cmd.CommandText = "CREATE TABLE " + table_name + "(date1 Text, data1 Text)";
                    m_cmd.ExecuteNonQuery();

                    MessageBox.Show("create table OK!");
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            m_sqlconn.Close();
        }

        private void Button_Click2(object sender, RoutedEventArgs e)
        {
            try
            {
                NewTable(db3_file, "mytable3");
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(string.Format("create table failed ### {0}", ex.Message));
            }            
        }

        private void Button_Click3(object sender, RoutedEventArgs e)
        {
            //https://blog.csdn.net/LIN943168247/article/details/103633083
            string[] arr1 = { "date1", "data1" };
            string[] arr2 = { "2022-09-20", "我是数据defg" };

            try
            {
                Add(arr1, arr2, g_table_name);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }            
        }

        static public void Add(string[] keys, string[] values, string table_name)
        {
            string keys_string = "(" + keys[0];
            string value_string = "('" + values[0] + "'";

            for (int i=1; i<keys.Length; i++)
            {
                keys_string += "," + keys[i];
            }

            for (int i=1; i<values.Length; i++)
            {
                value_string += ",'" + values[i] + "'";
            }

            keys_string += ")";
            value_string += ")";

            string sql1 = string.Format("INSERT INTO "+table_name+" {0} VALUES {1}", keys_string, value_string);

            try
            {
                m_cmd = new SQLiteCommand(sql1, m_sqlconn);
                int rows = m_cmd.ExecuteNonQuery();
                MessageBox.Show("添加成功" + rows + "行");
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {

        }

        private void Button_Click4(object sender, RoutedEventArgs e)
        {
            string[] key1 = {"date1","data1" };
            string[] val1 = { "2022-09-20", "我是数据defg" };

            try
            {
                Delete1(key1, val1, g_table_name);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }            
        }

        public static void Delete1(string[] keys, string[] values, string table_name)
        {
            string values_string = keys[0] + "='" + values[0] + "'";
            for (int i=1; i<values.Length; i++)
            {
                values_string += " And " + keys[i] + "='" + values[i] + "'";
            }
            string sql1 = string.Format("DELETE FROM "+table_name+" WHERE {0} ", values_string);
            
            try
            {
                m_cmd = new SQLiteCommand(sql1, m_sqlconn);
                int rows = m_cmd.ExecuteNonQuery();
                MessageBox.Show("删除成功！" + rows + "行");

            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}
